﻿using System.Collections.Generic;
using System.Data;
using Excel4DB.Util.Interfaces;

namespace Excel4DB.Util
{
    public class SqlDataBase : IDataBase
    {
        public bool ConnectionIsOpen()
        {
            return SqlHelper.CheckState();
        }

        public List<string> GetColumnsType(DataTable dataTable)
        {
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                List<string> list = new List<string>();
                foreach (DataColumn item in dataTable.Columns)
                {
                    list.Add(item.DataType.FullName);
                }
                return list;
            }
            return null;
        }

        public DataTable GetDataBase()
        {
            DataSet dataSet = SqlHelper.ExecuteDataset(ConfigHelper.Instance.ConStr, CommandType.Text, "select * from sysdatabases");
            if (dataSet != null && dataSet.Tables.Count > 0)
                return dataSet.Tables[0];
            return null;
        }

        public DataTable GetDataFromTable(string tableName, string whereStr = null)
        {
            string sql = $@"SELECT * FROM {tableName} Where 1=1 ";
            if (!string.IsNullOrWhiteSpace(whereStr)) sql += $"and {whereStr}";
            DataSet dataSet = SqlHelper.ExecuteDataset(ConfigHelper.Instance.ConStr, CommandType.Text, sql);
            if (dataSet != null && dataSet.Tables.Count > 0)
                return dataSet.Tables[0];
            return null;
        }

        public DataTable GetFields(string tableName)
        {
            DataSet dataSet = SqlHelper.ExecuteDataset(ConfigHelper.Instance.ConStr, CommandType.Text, $"select a.name as [column],b.name as type from syscolumns a,systypes b where a.id=object_id('{tableName}') and a.xtype=b.xtype");
            if (dataSet != null && dataSet.Tables.Count > 0)
                return dataSet.Tables[0];
            return null;
        }

        public DataTable GetTables()
        {
            DataSet dataSet = SqlHelper.ExecuteDataset(ConfigHelper.Instance.ConStr, CommandType.Text, @"SELECT * FROM SysObjects Where XType='U'");
            if (dataSet != null && dataSet.Tables.Count > 0)
                return dataSet.Tables[0];
            return null;
        }
    }
}
